<?php

namespace App\Console\Commands;

use App\Models\Teacher;
use Encore\Admin\Admin;
use Encore\Admin\Auth\Database\Administrator;
use Illuminate\Console\Command;
use DB;
use App\Models\Squad;
use App\Models\Crouse;
use App\Models\Classroom;
use App\Models\Notice;
use App\Models\Term;


class MonthSquad extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'monthsquads';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        //每月2号生成学分表
        $term = Term::where('status','0')->get()->toArray();

        $start_time = strtotime(date('Y-m-d 00:00:00', strtotime('-21 days')));
        $end_time = strtotime(date('Y-m-d 23:59:59', strtotime('-14 days')));

        $year = $season = $week = 0;
        $start_date = '';
        foreach($term as $v){
            $year = $v['year'];
            $season = $v['season'];
            $start_date = $v['start_date'];
        }

        $squads = DB::table('squads')->get(['id','start_date']);

        $week = ceil(floor(($end_time-strtotime($start_date))/60/60/24)/7)-1;

        foreach($squads as $value){
            if(strtotime($value->start_date) >= $end_time){
                continue;
            }
            //对每个班进行操作
            $squad_data = array();
            $squad_data['squad_id'] = $value->id;
            $squad_data['year'] = $year;
            $squad_data['season'] = $season;
            $squad_data['week'] = $week;
            $squad_data['month'] = '';
            $squad_data['create_time'] = date('Y-m-d H:i:s');

            //找到时间段内所有日程
            $notice = DB::table('notices')->whereIn('status',['2','4'])->where('squad_id',$value->id)
                ->whereRaw('UNIX_TIMESTAMP(`starttime`) >= '.$start_time)
                ->whereRaw('UNIX_TIMESTAMP(`starttime`) <= '.$end_time)->get(['id','notice_type']);
            //统计有多少次家校、作业、考勤、听写、测试
            $kq_notice = $zy_notice = $jx_notice = $test_notice = $dictation_notice = [];
            $kq_num = $zy_num = $jx_num = $test_num = $dictation_num = 0;
            foreach($notice as $v){
                switch ($v->notice_type) {
                    case '1': //考勤
                        $kq_notice[] = $v->id;
                        $kq_num++;
                        break;
                    case '2': //测试
                        $test_notice[] = $v->id;
                        $test_num ++;
                        break;
                    case '3': //家校
                        $jx_notice[] = $v->id;
                        $jx_num++;
                        break;
                    case '4': //听写
                        $dictation_notice[] = $v->id;
                        $dictation_num++;
                        break;
                    case '5': //作业
                        $zy_notice[] = $v->id;
                        $zy_num++;
                        break;
                    case '6': //教学进度确认
                        break;
                    default: break;
                }
            }
            // 考勤
            $good_num = $bad_num = 0;
            $kq = DB::table('attendance_records')->whereIn('notice_id',$kq_notice)->get();
            foreach($kq as $v){
                if($v->attendance_status == '1'){
                    $good_num ++ ;
                }else{
                    $bad_num ++;
                }
            }
            if($good_num+$bad_num == 0){
                $squad_data['kq_rate'] = round($good_num/1,2);
            }else{
                $squad_data['kq_rate'] = round($good_num/($good_num+$bad_num),2);
            }
            // 作业
            $good_num = $bad_num = 0;
            $zy = DB::table('zuoye_records')->whereIn('notice_id',$zy_notice)->get();
            foreach($zy as $v){
                if($v->zuoye_status == '1'){
                    $good_num ++ ;
                }else{
                    $bad_num ++;
                }
            }
            if($good_num+$bad_num == 0){
                $squad_data['zy_rate'] = round($good_num/1,2);
            }else{
                $squad_data['zy_rate'] = round($good_num/($good_num+$bad_num),2);
            }
            // 家校
            $good_num = $bad_num = 0;
            $jx = DB::table('jiaxiao_records')->whereIn('notice_id',$jx_notice)->get();
            foreach($jx as $v){
                if($v->jiaxiao_status == '1'){
                    $good_num ++ ;
                }else{
                    $bad_num ++;
                }
            }
            if($good_num+$bad_num == 0){
                $squad_data['jx_rate'] = round($good_num/1,2);
            }else{
                $squad_data['jx_rate'] = round($good_num/($good_num+$bad_num),2);
            }
            // 听写
            $good_num = $bad_num = 0;
            $dictation = DB::table('dictation_score_records')->whereIn('notice_id',$dictation_notice)->get();
            foreach($dictation as $v){
                if($v->dictation_status == '1'){
                    $good_num ++ ;
                }else{
                    $bad_num ++;
                }
            }
            if($good_num+$bad_num == 0){
                $squad_data['dictation_rate'] = round($good_num/1,2);
            }else{
                $squad_data['dictation_rate'] = round($good_num/($good_num+$bad_num),2);
            }
            // 测试
            $good_num = $bad_num = 0;
            $test = DB::table('test_score_records')->whereIn('notice_id',$test_notice)->get();
            foreach($test as $v){
                if($v->test_status == '1'){
                    $good_num ++ ;
                }else{
                    $bad_num ++;
                }
            }
            if($good_num+$bad_num == 0){
                $squad_data['test_rate'] = round($good_num/1,2);
            }else{
                $squad_data['test_rate'] = round($good_num/($good_num+$bad_num),2);
            }

            $lostCount = DB::table('applys')->whereIn('type',['2','3'])->where('status','=','1')->where('squad_id',$value->id)
                ->whereRaw('UNIX_TIMESTAMP(`sp_time`) >= '.$start_time)
                ->whereRaw('UNIX_TIMESTAMP(`sp_time`) <= '.$end_time)->count();
            $squad_data['wastage_num'] = $lostCount;

            $stunum = DB::table('students')->where('squad_id',$value->id) ->whereRaw('UNIX_TIMESTAMP(`enrollment_time`) <= '.$end_time)->count()
                - DB::table('applys')->whereIn('type',['2','3'])->where('status','=','1')->where('squad_id',$value->id)
                    ->whereRaw('UNIX_TIMESTAMP(`sp_time`) <= '.$end_time)->count();
            $squad_data['stu_num'] = $stunum+$lostCount;
            if($stunum + $lostCount == 0){
                $squad_data['wastage_rate'] = 0;
            }else{
                $squad_data['wastage_rate'] = $lostCount/($stunum + $lostCount);
            }

            $jindu = DB::table('notices as n')->join('study_jindu_records as sjr','n.id','=','sjr.notice_id')
                ->whereRaw('UNIX_TIMESTAMP(n.starttime) <= '.$end_time)
                ->where('sjr.squad_id',$value->id)
                ->orderBy('n.id','desc')
                ->select('sjr.jindu_content','n.id')
                ->first();
            if(!is_null($jindu)){
                /*                foreach($jindu as $v){
                                    if(isset($v->jindu_content)){
                                        $squad_data['study_jindu'] = $v->jindu_content;
                                    }
                                }*/
                $squad_data['study_jindu'] = $jindu->jindu_content;
            }

            $course = DB::table('crousesheets')->where('squads_id',$value->id)->where('status','=',1)->groupBy(['crouse_id','teacher_id'])->get(['crouse_id','teacher_id']);

            $temp = array();
            foreach($course as $v){
                $temp[] = array('course_name'=>Crouse::find($v->crouse_id)->name,'teacher_name'=>Administrator::find($v->teacher_id)->en_name);
            }
            $squad_data['content'] = json_encode($temp);
            // avg:jx,zy,test,dictation
            $squad_data['jx_avg'] = DB::table('jiaxiao_records')->whereIn("jiaxiao_status",["1","3"])
                ->whereIn("notice_id",$jx_notice)->where("jiaxiao_score",">",0)->avg("jiaxiao_score");
            $squad_data['zy_avg'] = DB::table('zuoye_records')->whereIn("zuoye_status",["1","3"])
                ->whereIn("notice_id",$zy_notice)->where("zuoye_score",">",0)->avg("zuoye_score");
            $squad_data['dictation_avg'] = DB::table('dictation_score_records')->where("dictation_status",'=','1')
                ->whereIn("notice_id",$dictation_notice)->where("dictation_score",">",0)->avg("dictation_score");


            //测试平均分需要进行折算
            $total_score = $total_num = 0;
            $test_data = DB::table('test_score_records')->where("test_status","=","1")
                ->whereIn("notice_id",$test_notice)->where("test_score",">",0)->get(['notice_id','test_score']);
            $test_rule = DB::table('notices as n')->join('test_assigns as ta','ta.id','=','n.test_assign_id')
                ->join('test_configs as tc','tc.id','=','ta.test_config_id')->select('n.id','tc.total_score')->get();
            $test_arr = array();
            foreach($test_rule as $v){
                $test_arr[$v->id] = $v->total_score;
            }

            foreach($test_data as $v){
                $total_score += $v->test_score / $test_arr[$v->notice_id] * 100;
                $total_num += 1;
            }
            if($total_num == 0){
                $squad_data['test_avg'] = 0;
            }else{
                $squad_data['test_avg'] = round($total_score/$total_num,10);
            }
            DB::table('squad_month')->insert($squad_data);
        }

    }

}
